/**
 * 
 */
package org.compiere.process;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.I_BPM_Project;
import org.compiere.model.MAttachmentEntry;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import extend.org.compiere.utils.Util;

/**
 * @author V.Sokolov
 *
 */
public class BPM_Consolid extends SvrProcess {
	
	/** Current context			*/
	private Properties m_ctx;
	int BPM_Project_ID = 0;
	int BPM_Form_ID = 0;
	int BPM_Subs_ID = 0;
	int BPM_Subs_ID2 = 0;
	private ProcessInfo pi;
	private static CLogger s_log = CLogger.getCLogger(BCS_ACReport.class);
	/* 
	 */
	@Override
	protected void prepare() {
		
		log.info(" The Sub form export Excel ");
		m_ctx = getCtx();
		pi = getProcessInfo();
		
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (name == null); 
				//
			else if (name.compareTo(I_BPM_Project.COLUMNNAME_BPM_Project_ID) == 0 && para[i].getParameter() != null)
				BPM_Project_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
//			else if (name.compareTo(I_BPM_Form.COLUMNNAME_BPM_Form_ID) == 0 && para[i].getParameter() != null)
//				BPM_Form_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
//			else if (name.compareTo(I_BPM_Subs.COLUMNNAME_BPM_Subs_ID) == 0 && para[i].getParameter() != null)
//				BPM_Subs_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
//			else if (name.compareTo("BPM_Subs_ID2") == 0 && para[i].getParameter() != null)
//				BPM_Subs_ID2 = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
//
			else
			{
				log.log(Level.INFO, "Unknown Parameter: " + name);
			}
		}
	}

	/* 
	 */
	@Override
	protected String doIt() throws Exception {
		
		String file = "BPM_Consolid_Final.xlsx";
		String filePath = Util.localFilePath + file;

		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file.substring(0,file.indexOf(".")));
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,
					"NotFoundTemplate"));

		InputStream inputStream = entry.getInputStream();  
		XSSFWorkbook wb = new XSSFWorkbook(inputStream);
		
		// go------------------------------
//		1bo	-	BPM_Form_ID=1000004
//		2bo	-	BPM_Form_ID=1000005
//		3bo	-	BPM_Form_ID=1000006
//		4bo	-	BPM_Form_ID=1000007
//		5bop	-	BPM_Form_ID=1000008
//		5bok	-	BPM_Form_ID=1000009
//		6bo	-	BPM_Form_ID=1000010
		
//		vgoo bdr - BPM_Form_ID=1000035	 =	2bdo
//		vgoo balance - BPM_Form_ID=1000034	=	6bo
//		vgoo bddc - BPM_Form_ID=1000033	=	5bop
		
		
		
		//1bo	-	BPM_Form_ID=1000004-----------------------------------------
		List<classline> list  = getDatas(BPM_Project_ID,	1000004, 	1000002, 1000005);
		List<Double> listVGOO  = getDatasVGOO(BPM_Project_ID,1000035);
		List<Integer> lines = getLines(list);
		XSSFSheet sheet = wb.getSheetAt(0);
		
		int row = 6;
		for(int i=0; i<lines.size(); i++){
				int col = 0;
				for(int j = 0; j < list.size(); j++){
						if(lines.get(i).equals(list.get(j).line)){
							Row r = getRow(sheet, row);
							Cell cell = getCell(r, col+++5);
							
							double s = .0;
							try{
								if(listVGOO.size() > i){
									if(	i == 0) s = listVGOO.get(0); 
									if(	i == 1) s = listVGOO.get(1);
									
									if(	i == 3) s = listVGOO.get(5);
									if(	i == 4) s = listVGOO.get(6);
									
									if(	i == 10) s = listVGOO.get(7);
									
									if(	i == 11) s = listVGOO.get(8);
									if(	i == 12) s = listVGOO.get(9);
	
								}
							}catch(Exception e){}
							
							cell.setCellValue(list.get(j).value.doubleValue() - s);
						}
				}
				row++;
		}
		
		//		2bo	-	BPM_Form_ID=1000005---------------------------------------
		//		vgoo bdr - BPM_Form_ID=1000035	 =	2bdo
		list  = getDatas(BPM_Project_ID,	1000005, 1000008, 1000011);
		lines = getLines(list);
		listVGOO  = getDatasVGOO(BPM_Project_ID,1000035);
		sheet = wb.getSheetAt(1);

		row = 6;
		for(int i=0; i<lines.size(); i++){
				int col = 0;
				for(int j = 0; j < list.size(); j++){
						if(lines.get(i).equals(list.get(j).line)){
							Row r = getRow(sheet, row);
							Cell cell = getCell(r, col+++4);
//							cell.setCellValue(list.get(j).value.doubleValue());
				 			if(listVGOO.size() > i && list.get(j).col == 1000008){
				 				cell.setCellValue(list.get(j).value.doubleValue() - listVGOO.get(i).doubleValue());
//				 				System.out.println(list.get(j).value.doubleValue() + ",	" + listVGOO.get(i).doubleValue());
				 			}
				 			else{
				 				cell.setCellValue(list.get(j).value.doubleValue());
//				 				System.out.println(list.get(j).value.doubleValue());
				 			}

						}
				}
				row++;
		}
		
		//		3bo	-	BPM_Form_ID=1000006--------------------------------------		
		list  = getDatas(BPM_Project_ID,	1000006,	1000014,	1000017);
		lines = getLines(list);
		listVGOO  = getDatasVGOO(BPM_Project_ID,1000035);
		sheet = wb.getSheetAt(2);

		row = 7;
		for(int i=0; i<lines.size(); i++){
				int col = 0;
				for(int j = 0; j < list.size(); j++){
						if(lines.get(i).equals(list.get(j).line)){
							Row r = getRow(sheet, row);
							Cell cell = getCell(r, col+++5);

							double s = .0;
							try{
								if(listVGOO.size() > i){
									if(	i == 0) s = listVGOO.get(2); 
									if(	i == 1) s = listVGOO.get(3);
									
									if(	i == 42) s = listVGOO.get(10);
									if(	i == 44) s = listVGOO.get(11);
									
									if(	i == 93) s = listVGOO.get(12);
									
									if(	i == 96) s = listVGOO.get(15);
									if(	i == 99) s = listVGOO.get(21);
	
								}
							}catch(Exception e){}

							
							cell.setCellValue(list.get(j).value.doubleValue() - s);
//							cell.setCellValue(list.get(j).value.doubleValue());
						}
				}
				row++;
		}		
		
		//		4bo	-	BPM_Form_ID=1000007-------------------------------------
		list  = getDatas(BPM_Project_ID,	1000007,	1000020,	1000023);
		lines = getLines(list);
		sheet = wb.getSheetAt(3);

		row = 6;
		for(int i=0; i<lines.size(); i++){
				int col = 0;
				for(int j = 0; j < list.size(); j++){
						if(lines.get(i).equals(list.get(j).line)){
							Row r = getRow(sheet, row);
							Cell cell = getCell(r, col+++5);
							cell.setCellValue(list.get(j).value.doubleValue());
						}
				}
				row++;
		}		
		
		//		5bop	-	BPM_Form_ID=1000008--------------------------------
		//		vgoo bddc - BPM_Form_ID=1000033	=	5bop		
		list  = getDatas(BPM_Project_ID,	1000008,	1000026,	1000029);
		lines = getLines(list);
		listVGOO  = getDatasVGOO(BPM_Project_ID,1000033);
		sheet = wb.getSheetAt(4);

		row = 6;
		for(int i=0; i<lines.size(); i++){
				int col = 0;
				for(int j = 0; j < list.size(); j++){
						if(lines.get(i).equals(list.get(j).line)){
							Row r = getRow(sheet, row);
							Cell cell = getCell(r, col+++2);
//							cell.setCellValue(list.get(j).value.doubleValue());
				 			if(listVGOO.size() > i && list.get(j).col == 1000026)
				 				cell.setCellValue(list.get(j).value.doubleValue() - listVGOO.get(i).doubleValue());
				 			else
				 				cell.setCellValue(list.get(j).value.doubleValue());
							
						}
				}
				row++;
		}
		
		
		
//		5bok	-	BPM_Form_ID=1000009-------------------------------------
		list  = getDatas(BPM_Project_ID,	1000009,	1000032,	1000035);
		lines = getLines(list);
		sheet = wb.getSheetAt(5);

		row = 6;
		for(int i=0; i<lines.size(); i++){
				int col = 0;
				for(int j = 0; j < list.size(); j++){
						if(lines.get(i).equals(list.get(j).line)){
							Row r = getRow(sheet, row);
							Cell cell = getCell(r, col+++2);
							cell.setCellValue(list.get(j).value.doubleValue());
						}
				}
				row++;
		}		
		
		//		6bo	-	BPM_Form_ID=1000010-----------------------------------------
		//		vgoo balance - BPM_Form_ID=1000034	=	6bo
		list  = getDatas(BPM_Project_ID,	1000010,	1000038,	1000041);
		lines = getLines(list);
		listVGOO  = getDatasVGOO(BPM_Project_ID,1000034);
		sheet = wb.getSheetAt(6);

		row = 6;
		for(int i=0; i<lines.size(); i++){
				int col = 0;
				for(int j = 0; j < list.size(); j++){
						if(lines.get(i).equals(list.get(j).line)){
							Row r = getRow(sheet, row);
							Cell cell = getCell(r, col+++2);
//							cell.setCellValue(list.get(j).value.doubleValue());
				 			if(listVGOO.size() > i && list.get(j).col == 1000038)
				 				cell.setCellValue(list.get(j).value.doubleValue() - listVGOO.get(i).doubleValue());
				 			else
				 				cell.setCellValue(list.get(j).value.doubleValue());
							
						}
				}
				row++;
		}
		
		
//		List<Double> listVGOO  = getDatasVGOO(BPM_Project_ID,1000035, BPM_Subs_ID);
		
		
		
		
		// end -------------------------------
		wb.setForceFormulaRecalculation(true);
		FileOutputStream fileOut = new FileOutputStream(filePath);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
		if(!Env.isWindows())
			Util.launchFile(new File(filePath));
		else{
			try {
				String command = "excel \"" + filePath + "\"";
				Runtime.getRuntime().exec("cmd /c start " + command);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return Msg.translate(m_ctx, "Success");
	}

	private List<Integer> getLines(List<classline> list) {
		List<Integer> result = new ArrayList<Integer>();
		for(int i=0; i < list.size(); i++)
			if(result.indexOf(list.get(i).line) == -1)
				result.add(list.get(i).line);
		
		return result;
	}

	private List<Double> getDatasVGOO(int PM_Project_ID, int PM_Form_ID) {

		List<Double> result = new ArrayList<Double>();
		
		String sql =  
				"	select	\n"	+ 
				"	line.bpm_formline_id,	v.cellvalue		\n"	+ 
				"	from adempiere.bpm_formvalues v		\n"	+
				"	inner join adempiere.bpm_formline line on line.BPM_Formline_ID = v.BPM_Formline_ID		\n"	+
				"	where v.BPM_Form_ID = "+	PM_Form_ID	+"		\n"	+
				"	and v.bpm_project_id = "+	PM_Project_ID	+"		\n"	+
//				"	and v.bpm_subs_id = "	+PM_Subs_ID+	"			\n"	+
//				"	group by line.bpm_formline_id		\n"	+
				"	order by line.bpm_formline_id		\n"	
		;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				result.add(rs.getDouble(2));
				
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		
		return result;
		
	
	}
	
	
	private List<classline> getDatas(int PM_Project_ID, int PM_Form_ID, int col1, int col2) {

		List<classline> result = new ArrayList<classline>();
		
		String sql =  
				"select \n" +
						"	line.bpm_formline_id\n" +
						"	,cel.bpm_formcolumn_id	\n"	+	
						"	,sum(v.cellvalue) \n" +
						"from adempiere.BPM_Form form\n" +
						"inner join adempiere.bpm_formline line on line.BPM_Form_ID = form.BPM_Form_ID\n" +
						"inner join adempiere.bpm_formcell cel on cel.BPM_Formline_ID = line.BPM_Formline_ID \n" +
						"left join adempiere.bpm_formvalues v on v.bpm_form_id = form.bpm_form_id \n" +
						"	and v.bpm_formline_id = line.bpm_formline_id \n" +
						"	and v.bpm_formcell_id = cel.bpm_formcell_id\n" +
						"	and v.bpm_formcolumn_id = v.bpm_formcolumn_id\n" +
						"where form.BPM_Form_ID="+PM_Form_ID+"	\n" +
//						"and cel.bpm_formcolumn_id between 1000002 and 1000005	\n" +
						"and cel.bpm_formcolumn_id between "+col1+" and "+col2+"	\n" +
						"and v.bpm_project_id = "+PM_Project_ID+"\n" +
//						"and v.bpm_subs_id in ("+BPM_Subs_ID+", "+BPM_Subs_ID2+")\n" +
						"group by line.bpm_formline_id,	cel.bpm_formcolumn_id		\n" +
						"order by line.bpm_formline_id, cel.bpm_formcolumn_id		"
		;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				result.add(new classline(rs.getInt(1), rs.getInt(2), rs.getBigDecimal(3)));
//				System.out.println(rs.getInt(1) +", "+ rs.getInt(2) +", "+ rs.getObject(3));
				
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		
		return result;
		
	
	}

	protected Row getRow(XSSFSheet sheet, int row){
		Row result = null;
		
		if(sheet != null){
			Row r = sheet.getRow(row);
			if(r == null)
				r = sheet.createRow(row);
			
			result = r;
		}
		
		return result;
	}	
	
	protected Cell getCell(Row r, int col){
		Cell result = null;
		if(r!=null){
			Cell c = r.getCell(col);
			if(c == null)
				c = r.createCell(col);
			
			result = c;
		}
		
		return result;
	}
	
}

class classline {
	
	int line = 0;
	int col = 0;
	BigDecimal value = Env.ZERO;
	
	classline(int l, int c, BigDecimal value){
		this.line = l; 
		this.col = c;
		this.value = value;
	}
	
}
